﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace SpringStudio.DAL.PaginationHandler
{
    /// <summary>
    /// 数据分页分析的Mssql实现
    /// </summary>
    internal class PaginationAnalysisMssql
    {
        internal static DataSet GetDataSet(string connectionString, string tableName, List<string> primaryKeys, List<PaginationOrderField> orderFields, List<string> columns, string selectCondition, List<PaginationSqlParameter> sqlParameters, int pageSize, int pageIndex, int recordCount)
        {
            /**
             * 步骤一：构建临时表的语句，临时表中的记录条数到当前页面结尾为止
             **/
            string sqlTempTable = "(select top " + (pageIndex * pageSize).ToString() + " ";

            // 1.主键包括在临时表的列中
            foreach (string primaryKey in primaryKeys)
            {
                sqlTempTable += primaryKey + ",";
            }

            // 2.排序字段也包含在临时表的列中（如果与主键重复，则不要重复添加）
            foreach (PaginationOrderField orderField in orderFields)
            {
                if (!PaginationValidationCheck.IsPrimaryKeysContainOrderField(primaryKeys, orderField))
                {
                    sqlTempTable += orderField.FieldName + ",";
                }
            }

            sqlTempTable = sqlTempTable.Substring(0, sqlTempTable.Length - 1); // 去掉逗号

            // 3.从主表中取记录
            sqlTempTable += " from " + tableName;

            // 如果是组合出来的表名，此处要为它附加一个别名
            if (tableName.IndexOf("(") > -1 && tableName.IndexOf(")") > -1)
                sqlTempTable += " as Page_Table_AliasName";

            // 4.查询条件
            if (!"".Equals(selectCondition))
            {
                sqlTempTable += " where " + selectCondition;
            }

            // 5.排序字段
            sqlTempTable += " order by ";
            foreach (PaginationOrderField orderField in orderFields)
            {
                sqlTempTable += orderField.FieldName + " " + orderField.Direction + ",";
            }

            // 6.对于未设为排序字段的主键，要以升序“asc”排序（其实主键默认就是升序）
            foreach (string primaryKey in primaryKeys)
            {
                if (!PaginationValidationCheck.IsOrderFieldsContainPrimaryKey(orderFields, primaryKey))
                {
                    sqlTempTable += primaryKey + " asc,";
                }
            }

            sqlTempTable = sqlTempTable.Substring(0, sqlTempTable.Length - 1); // 去掉逗号

            sqlTempTable += ") as tmptb";

            /**
             * 步骤二：从临时表中读出当前页面的记录（仅包含主键，且为倒序），存为新的临时表
             * 注意：此时要考虑一种情况，即当前页是尾页的时候，则记录条数可能小于页面大小的数值
             **/
            string sqlKeysTable = string.Empty;
            if (recordCount - pageIndex * pageSize < 0)
            {
                sqlKeysTable = "(select top " + (recordCount - (pageIndex - 1) * pageSize).ToString() + " ";
            }
            else
            {
                sqlKeysTable = "(select top " + pageSize.ToString() + " ";
            }

            // 1.主键
            foreach (string primaryKey in primaryKeys)
            {
                sqlKeysTable += primaryKey + ",";
            }

            sqlKeysTable = sqlKeysTable.Substring(0, sqlKeysTable.Length - 1); // 去掉逗号

            // 2.从临时表中取主键
            sqlKeysTable += " from " + sqlTempTable;

            // 3.排序字段（倒序）
            sqlKeysTable += " order by ";
            foreach (PaginationOrderField orderField in orderFields)
            {
                string reverseSort = ("asc".Equals(orderField.Direction)) ? "desc" : "asc";
                sqlKeysTable += orderField.FieldName + " " + reverseSort + ",";
            }

            // 4.对于未设为排序字段的主键，要以降序“desc”排序（因为未设排序的话，主键默认是升序，倒过来就是降序）
            foreach (string primaryKey in primaryKeys)
            {
                if (!PaginationValidationCheck.IsOrderFieldsContainPrimaryKey(orderFields, primaryKey))
                {
                    sqlKeysTable += primaryKey + " desc,";
                }
            }

            sqlKeysTable = sqlKeysTable.Substring(0, sqlKeysTable.Length - 1); // 去掉逗号

            sqlKeysTable += ") as b";

            /**
             * 步骤三：取出正续排列的当前页记录（使用内联接）
             **/
            // 1.要查询的列
            string sqlResult = "select ";
            if (columns.Count > 0)
            {
                foreach (string column in columns)
                {
                    sqlResult += "a." + column + ",";
                }

                sqlResult = sqlResult.Substring(0, sqlResult.Length - 1); // 去掉逗号
            }
            else
            {
                sqlResult += "a.*";
            }

            // 2.表的内联接
            sqlResult += " from " + tableName + " as a inner join " + sqlKeysTable + " on ";

            // 3.比较主键（如：a.id = b.id）
            foreach (string primaryKey in primaryKeys)
            {
                sqlResult += "a." + primaryKey + " = b." + primaryKey + " and ";
            }
            sqlResult = sqlResult.Substring(0, sqlResult.Length - 5); // 去掉" and "

            // 4.排序字段
            sqlResult += " order by ";
            foreach (PaginationOrderField orderField in orderFields)
            {
                sqlResult += "a." + orderField.FieldName + " " + orderField.Direction + ",";
            }

            // 5.对于未设为排序字段的主键，要以升序“asc”排序（其实主键默认就是升序）
            foreach (string primaryKey in primaryKeys)
            {
                if (!PaginationValidationCheck.IsOrderFieldsContainPrimaryKey(orderFields, primaryKey))
                {
                    sqlResult += "a." + primaryKey + " asc,";
                }
            }
            sqlResult = sqlResult.Substring(0, sqlResult.Length - 1); // 去掉逗号

            /**
             * 步骤四：添加查询参数
             **/
            IDbDataParameter[] arrParam = new IDbDataParameter[sqlParameters.Count];
            int i = 0;
            foreach (PaginationSqlParameter sqlParameter in sqlParameters)
            {
                arrParam.SetValue(SqlHelper.NewSqlParameter(sqlParameter.ParamName, sqlParameter.ParamValue), i);
                i++;
            }

            /**
             * 步骤五：执行Sql语句，取出数据集
             **/
            return SqlHelper.ExecuteDataSet(connectionString, CommandType.Text, sqlResult, arrParam);
        }

        internal static int GetRecordCount(string connectionString, string tableName, string selectCondition, List<PaginationSqlParameter> sqlParameters)
        {
            string sql = "select count(*) from " + tableName;

            // 如果是组合出来的表名，此处要为它附加一个别名
            if (tableName.IndexOf("(") > -1 && tableName.IndexOf(")") > -1)
            {
                sql += " as Page_Table_AliasName";
            }

            if (!"".Equals(selectCondition))
            {
                sql += " where " + selectCondition;
            }

            IDbDataParameter[] arrParam = new IDbDataParameter[sqlParameters.Count];
            int i = 0;
            foreach (PaginationSqlParameter sqlParameter in sqlParameters)
            {
                arrParam.SetValue(SqlHelper.NewSqlParameter(sqlParameter.ParamName, sqlParameter.ParamValue), i);
                i++;
            }

            DataSet ds = SqlHelper.ExecuteDataSet(connectionString, CommandType.Text, sql, arrParam);

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return int.Parse(ds.Tables[0].Rows[0][0].ToString());
            }
            else
            {
                throw new Exception("数据库访问错误，无法读取总记录条数！");
            }
        }

        internal static DataSet GetTotalDataSet(string connectionString, string tableName, List<string> primaryKeys, List<PaginationOrderField> orderFields, List<string> columns, string selectCondition, List<PaginationSqlParameter> sqlParameters)
        {
            string sql = "select ";

            // 要查询的列
            if (columns.Count > 0)
            {
                foreach (string column in columns)
                {
                    sql += column + ",";
                }

                sql = sql.Substring(0, sql.Length - 1); // 去掉逗号
            }
            else
            {
                sql += "*";
            }

            sql += " from " + tableName;

            // 如果是组合出来的表名，此处要为它附加一个别名
            if (tableName.IndexOf("(") > -1 && tableName.IndexOf(")") > -1)
            {
                sql += " as Page_Table_AliasName";
            }

            if (!"".Equals(selectCondition))
            {
                sql += " where " + selectCondition;
            }

            IDbDataParameter[] arrParam = new IDbDataParameter[sqlParameters.Count];
            int i = 0;
            foreach (PaginationSqlParameter sqlParameter in sqlParameters)
            {
                arrParam.SetValue(SqlHelper.NewSqlParameter(sqlParameter.ParamName, sqlParameter.ParamValue), i);
                i++;
            }

            // 排序字段
            sql += " order by ";
            foreach (PaginationOrderField orderField in orderFields)
            {
                sql += orderField.FieldName + " " + orderField.Direction + ",";
            }

            // 对于未设为排序字段的主键，要以升序“asc”排序（其实主键默认就是升序）
            foreach (string primaryKey in primaryKeys)
            {
                if (!PaginationValidationCheck.IsOrderFieldsContainPrimaryKey(orderFields, primaryKey))
                {
                    sql += primaryKey + " asc,";
                }
            }
            sql = sql.Substring(0, sql.Length - 1); // 去掉逗号

            return SqlHelper.ExecuteDataSet(connectionString, CommandType.Text, sql, arrParam);
        }
    }
}
